package com.java.dao;


import com.java.vo.ShopProduct;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


@Repository
public class ProductDao {

	private Logger logger = LoggerFactory.getLogger(getClass());

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public int insertShopProduct(ShopProduct shopProduct) {
		final String sql = "INSERT INTO shop_product( name, label, type, fictitious_type, ticket_system_id, "
				+ " weight, img_big_url, img_small_url, price_type, price_cash, "
				+ " price_point, price_market, unit, product_num, inventory_num, "
				+ " show_saled_num,  content, is_up, is_send,  created_at, "
				+ " updated_at, real_saled_num) VALUES (?, ?, ?, ?, ?,   ?, ?, ?, ?, ?,  ?, ?, ?, ?, ?,  ?, ?, ?, ?, NOW(), "
				+ " NOW(), ?)";
		System.out.println("插入产品sql： " + sql);
		System.out.println("插入参数： " + shopProduct.toString());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{	
			jdbcTemplate.update(new PreparedStatementCreator() {
	
				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, shopProduct.getName());
					ps.setString(2, shopProduct.getLabel());
					ps.setLong(3, shopProduct.getType());
					ps.setLong(4, shopProduct.getFictitiousType());
					ps.setLong(5, shopProduct.getTicketSystemId());
					
					ps.setInt(6, shopProduct.getWeight());
					ps.setString(7, ifNULL(shopProduct.getImgBigUrl()));
					ps.setString(8, ifNULL(shopProduct.getImgSmallUrl()));
					ps.setInt(9, shopProduct.getPriceType());
					ps.setLong(10, shopProduct.getPriceCash());
					
					ps.setLong(11, shopProduct.getPricePoint());
					ps.setLong(12, shopProduct.getPriceMarket());
					ps.setString(13, ifNULL(shopProduct.getUnit()));
					ps.setLong(14, shopProduct.getProductNum());
					ps.setInt(15, shopProduct.getInventoryNum());
					
					ps.setInt(16, shopProduct.getShowSaledNum());
					ps.setString(17, ifNULL(shopProduct.getContent()));
					ps.setInt(18, shopProduct.getIsUp());
					ps.setInt(19, shopProduct.getIsSend());
					ps.setInt(20, shopProduct.getRealSaledNum());
					return ps;
				}
			}, keyHolder);
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.toString());
			return -1;
		}
		return keyHolder.getKey().intValue();
	}
	
	public boolean setShelf(int id, int isUpInt) {
		final String sql = " UPDATE shop_product SET is_up = ?, updated_at = NOW() WHERE id = ? ";
		System.out.println("更新产品sql： " + sql);
		
		System.out.println("更新产品参数： " + id + "|" + isUpInt);
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{	
			return jdbcTemplate.update(new PreparedStatementCreator() {
	
				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setInt(1, isUpInt);
					ps.setInt(2, id);
					return ps;
				}
			}, keyHolder) == 1;
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.toString());
			return false;
		}
	}
	
	public int UpdateShopProduct(ShopProduct shopProduct) {
		final String sql = " UPDATE shop_product SET name = ?, label = ?, type = ?,  fictitious_type = ?,  ticket_system_id = ?,  "
				+ " weight = ?,  img_big_url = ?,  img_small_url = ?,  price_type = ?,  price_cash = ?,  "
				+ " price_point = ?,  price_market = ?,  unit = ?,  product_num = ?,  inventory_num = ?,  "
				+ " show_saled_num = ?, content = ?,  is_up = ?,  is_send = ?, "
				+ " updated_at = NOW(), real_saled_num = ?, content_type = ? WHERE id = ? ";
		System.out.println("更新产品sql： " + sql);
		System.out.println("更新参数： " + shopProduct.toString());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{	
			int update = jdbcTemplate.update(new PreparedStatementCreator() {
	
				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, shopProduct.getName());
					ps.setString(2, shopProduct.getLabel());
					ps.setLong(3, shopProduct.getType());
					ps.setLong(4, shopProduct.getFictitiousType());
					ps.setLong(5, shopProduct.getTicketSystemId());
					
					ps.setInt(6, shopProduct.getWeight());
					ps.setString(7, shopProduct.getImgBigUrl());
					ps.setString(8, shopProduct.getImgSmallUrl());
					ps.setInt(9, shopProduct.getPriceType());
					ps.setLong(10, shopProduct.getPriceCash());
					
					ps.setLong(11, shopProduct.getPricePoint());
					ps.setLong(12, shopProduct.getPriceMarket());
					ps.setString(13, shopProduct.getUnit());
					ps.setLong(14, shopProduct.getProductNum());
					ps.setInt(15, shopProduct.getInventoryNum());
					
					ps.setInt(16, shopProduct.getShowSaledNum());
					ps.setString(17, shopProduct.getContent());
					ps.setInt(18, shopProduct.getIsUp());
					ps.setInt(19, shopProduct.getIsSend());
					ps.setInt(20, shopProduct.getRealSaledNum());
					ps.setInt(21, shopProduct.getContentType());
					ps.setInt(22, shopProduct.getId());
					return ps;
				}
			}, keyHolder);
			System.out.println(keyHolder.toString());
			return update;
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.toString());
			return -1;
		}
	}
	
	
	public ShopProduct getShopProductById( int productId) {
		final String sql = "SELECT * FROM shop_product  WHERE id = ? ";
		
		try {
			ShopProduct info = jdbcTemplate.queryForObject(sql, new Object[]{productId}, new RowMapper<ShopProduct>() {

				@Override
				public ShopProduct mapRow(ResultSet rs, int rowNum) throws SQLException {
					ShopProduct info = setShopProduct(rs);
					return info;
				}
			});
			
			return info;
		} catch (DataAccessException e) {
			logger.info("getShopProductByCode e = " + e.getLocalizedMessage());
		}
		
		return null;
	}
	
	private ShopProduct setShopProduct(ResultSet rs) throws SQLException{
		ShopProduct info = new ShopProduct();
		info.setId(rs.getInt("id"));
		info.setName(rs.getString("name"));
		info.setLabel(rs.getString("label"));
		info.setType(rs.getInt("type"));
		info.setFictitiousType(rs.getInt("fictitious_type"));
		info.setTicketSystemId(rs.getInt("ticket_system_id"));
		info.setWeight(rs.getInt("weight"));
		info.setImgBigUrl(rs.getString("img_big_url"));
		info.setImgSmallUrl(rs.getString("img_small_url"));
		info.setPriceType(rs.getInt("price_type"));
		info.setPriceCash(rs.getLong("price_cash"));
		
		info.setPricePoint(rs.getLong("price_point"));
		info.setPriceMarket(rs.getLong("price_market"));
		info.setUnit(rs.getString("unit"));
		info.setProductNum(rs.getLong("product_num"));
		info.setInventoryNum(rs.getInt("inventory_num"));
		info.setRealSaledNum(rs.getInt("real_saled_num"));
		info.setShowSaledNum(rs.getInt("show_saled_num"));
		info.setContent(rs.getString("content"));
		info.setContentType(rs.getInt("content_type"));
		info.setIsUp(rs.getInt("is_up"));
		info.setIsSend(rs.getInt("is_send"));
		info.setUpDate(rs.getDate("up_date"));
		info.setCreatedAt(rs.getDate("created_at"));
		info.setUpdatedAt(rs.getDate("updated_at"));
		return info;
	}
	
	public List<ShopProduct> getProductActiveList() {
		StringBuffer sql = new StringBuffer("SELECT  * FROM shop_product WHERE is_up = true ");
		try {
			List<ShopProduct> users = jdbcTemplate.query(sql.toString(),
					new ResultSetExtractor<List<ShopProduct>>() {
						public List<ShopProduct> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopProduct> list = new ArrayList<ShopProduct>();
							while (rs.next()) {
							ShopProduct info = setShopProduct(rs);
							list.add(info);
						}
						return list;
				}
			});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist ShopProduct by all = "  + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
	private void getShopProductListCondition(StringBuffer sql,ArrayList<Object> parmList,String name,Integer isSend,
			String label,String content){
		if (!StringUtils.isEmpty(name)) {
			sql.append(" AND name like  ");
			sql.append(" '%" + name + "%' ");
		}
		
		if (isSend != null && isSend >= 0) {
			sql.append(" AND is_send =  ");
			sql.append(isSend);
		}
		
		if (!StringUtils.isEmpty(label)) {
			sql.append(" AND label like  ");
			sql.append(" '%" + label + "%' ");
		}
		
		if(!StringUtils.isEmpty(content)){
			sql.append(" AND ( name LIKE ");
			sql.append("'%" + content + "%' ");
			sql.append(" OR label LIKE '%" + content + "%'  ) ");
		}
	}
	
	public List<ShopProduct> getShopProductByAll(String name,Integer isSend,
			String label,String content,Integer page) {
		
		StringBuffer sql = new StringBuffer("SELECT  * FROM shop_product WHERE 1=1 "
				+ " AND img_small_url <> '-' AND  img_small_url <> '' "
				+ " AND content <> '' AND content <> '-' ");
		ArrayList<Object> parmList = new ArrayList<Object>();
		
		getShopProductListCondition(sql,parmList,name,isSend,label,content);
		
		if (page != null && page > 0) {
			sql.append(" ORDER BY id DESC LIMIT ").append(10 * (page - 1)).append(",").append(10);
		}else{
			sql.append(" ORDER BY id DESC");
		}
		System.out.println("查询产品sql： " + sql);
		System.out.println("查询参数： " +  name+ " | "+ isSend+ " | "+  label+ " | "+  content+ " | "+  page);
		
		try {
			List<ShopProduct> users = jdbcTemplate.query(sql.toString(), parmList.toArray(new Object[parmList.size()]),
					new ResultSetExtractor<List<ShopProduct>>() {
						public List<ShopProduct> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopProduct> list = new ArrayList<ShopProduct>();
							while (rs.next()) {
							ShopProduct info = setShopProduct(rs);
							list.add(info);
						}
						return list;
				}
			});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist ShopProduct by all = "  + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
	public Integer getShopProductCnt(String name,Integer isSend,
			String label,String content,Integer page) {
		
		StringBuffer sql = new StringBuffer("SELECT  count(1) cnt FROM shop_product WHERE "
				+ " img_small_url <> '-' AND  img_small_url <> '' "
				+ " AND content <> '' AND content <> '-' ");
		ArrayList<Object> parmList = new ArrayList<Object>();
		
		getShopProductListCondition(sql,parmList,name,isSend,label,content);
		System.out.println("查询产品sql： " + sql);
		System.out.println("查询参数： " +  name+ " | "+ isSend+ " | "+  label+ " | "+  content+ " | "+  page);
		Integer count = jdbcTemplate.query(sql.toString(), parmList.toArray(new Object[parmList.size()]), (rs) -> {
			Integer i = null;
			while (rs.next()) {
				i = rs.getInt("cnt");
			}
			return i;
		});
		return count;
	}
	
	private String ifNULL(String s){
		return s == null ? "-" : s;
	}
	
	public List<Map<String, Object>> checkProduct3IN1(int productId) {
		if( 0 == productId){
			return null;
		}
		final String sql = "SELECT detail_group_id detailGroupId, home_template_id homeTemplateId "
				+ " FROM shop_home_template_detail where product_id = ? AND template_product_type = 1 AND detail_group_id IS NOT NULL";
		try {
			return jdbcTemplate.queryForList(sql, productId);
		} catch (DataAccessException e) {
			logger.error("not exist checkProduct3IN1 by all = " + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
	public Boolean cancelTemplateDetail3IN1(String detailGroupId, String homeTemplateId) {

		final String sql = " UPDATE shop_home_template_detail SET status = false "
				+ " WHERE detail_group_id = ? AND home_template_id = ? "
				+ " AND template_product_type = 1 ";
		try {
			jdbcTemplate.update(sql, new Object[] { detailGroupId, homeTemplateId});
			return true;
		} catch (DataAccessException e) {
			logger.error("not exist cancelTemplateDetail3IN1 by all = " + "|" + e.getLocalizedMessage());
			return false;
		}
	}
	
	public Boolean cancelTemplateDetailByProduct(int productId) {
		if(productId <= 0){
			return false;
		}
		
		final String sql = " UPDATE shop_home_template_detail SET status = false ,updated_at = NOW() "
				+ " WHERE product_id = ? ";
		System.out.println("查询产品sql： " + sql);
		System.out.println("查询参数： " +  productId);
		try {
			jdbcTemplate.update(sql, new Object[] {productId});
			return true;
		} catch (DataAccessException e) {
			logger.error("fail cancelTemplateDetailByProduct by productId = "+ productId + "|" + e.getLocalizedMessage());
			return false;
		}
	}
	
	
}